﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using TestSapco;

public partial class Sync : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        message.Text = string.Empty;
    }
    protected void SyncSAP_Button_Click(object sender, EventArgs e)
    {

        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SRM"].ConnectionString))
        {
            SAPB2E sap = new SAPB2E();
            System.Data.DataSet ds = new DataSet();
            System.Data.SqlClient.SqlCommand cmd = conn.CreateCommand();
            System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.CommandText = "SELECT * FROM SAP_CodeType WHERE SapFunctionName IS NOT NULL";
            da.Fill(ds);
            DataTable dtCodeTypes = ds.Tables[0];

            conn.Open();
            foreach (DataRow row in dtCodeTypes.Rows)
            {
                DataTable sapDataTable = sap.GetDataTableFromFunction(
                    row.Field<string>("SapFunctionName"),
                    row.Field<string>("Id"),
                    row.Field<bool>("HasParentId1"),
                    row.Field<bool>("HasParentId2"));

                using (System.Data.SqlClient.SqlTransaction sqlTran = conn.BeginTransaction())
                {
                    cmd.CommandText = string.Format("DELETE FROM SAP_Code WHERE CodeTypeId = '{0}'", row["Id"].ToString());
                    cmd.Transaction = sqlTran;
                    cmd.ExecuteNonQuery();

                    using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(conn, System.Data.SqlClient.SqlBulkCopyOptions.Default, sqlTran))
                    {
                        bcp.DestinationTableName = "SAP_Code";
                        bcp.WriteToServer(sapDataTable);
                    }

                    sqlTran.Commit();
                } // using (System.Data.SqlClient.SqlTransaction sqlTran = conn.BeginTransaction())
            } // foreach (DataRow row in dtCodeTypes.Rows)

            message.Text = "All done.";
        } // using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["SRM"].ConnectionString))
    }
}